package com.ttech.twitter.util;

import com.ttech.twitter.bean.GraphData;
import com.ttech.twitter.bean.TweetDbObject;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * User: TTACANSEVER
 */
public class DbUtil {

    private static Connection dbConnection;
    private static final String sql = "insert into tweet (tweet_id, username, text, latitude, longitude, profile_image_url, type, tweet_time) values(?, ?, ?, ?, ?, ?, ? ,?)";
    private static final String graphSql = " SELECT " +
                                          "     count(1), " +
                                          "     count(case when type = 'medical' then 1 end) medical, " +
                                          "     count(case when type = 'service' then 1 end) service, " +
                                          "     count(case when type = 'transportation' then 1 end) transportation " +
                                          " FROM tweet where tweet_time > NOW() - INTERVAL 10 MINUTE";

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            dbConnection = DriverManager.getConnection("jdbc:mysql://localhost/justforist?autoReconnect=true&useUnicode=true&characterEncoding=utf8", "root", "");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void insertTweet(TweetDbObject object) throws SQLException {
        PreparedStatement ps = dbConnection.prepareStatement(sql);
        ps.setLong(1, object.getTweetId());
        ps.setString(2, object.getScreenName());
        ps.setString(3, object.getTweetText());
        ps.setDouble(4, object.getLatitude());
        ps.setDouble(5, object.getLongitude());
        ps.setString(6, object.getProfileImageUrl());
        ps.setString(7, object.getType());
        ps.setTimestamp(8, object.getTweetTime());
        ps.execute();
        ps.close();
    }

    public static List<GraphData> getGraphData() throws SQLException {
        List<GraphData> graphDatas = new ArrayList<GraphData>();
        PreparedStatement ps = dbConnection.prepareStatement(graphSql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            GraphData medicalData = new GraphData("Medical", rs.getInt("medical"));
            GraphData transportationData = new GraphData("Transportation", rs.getInt("transportation"));
            GraphData serviceData = new GraphData("Service", rs.getInt("service"));
            graphDatas.add(medicalData);
            graphDatas.add(transportationData);
            graphDatas.add(serviceData);
        }
        return graphDatas;
    }

}
